﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OSISStudentEditBusinessLayer;
using System.Data;
using OpenSISDataConnection;

namespace OSISStudentEditDataLayer
{
    public class StudentEditInfoDL
    {
        public StudentEditInfoDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

        public System.Data.DataTable populatefieldvalue(StudentEditInfoBL FieldsValues)
        {

            DataTable dtDefaultfieldsvalue = null;
            DataTable dtCoustmfieldsvalue = null;

            //  dtfieldsvalue = DataConnection.ConnectionPath.Getdata("select value,field_id,field_category from student_details where cat_id=" + FieldsValues.CatId + " and std_id =" + FieldsValues.StudentID + "").Tables[0];
            //dtfieldsvalue = DataConnection.ConnectionPath.Getdata("select SD.value,SD.field_id,SD.field_category from student_details SD left outer join Student_Setup_FormLayout_Order SSFO on SD.field_id=SSFO.Field_Id AND SD.field_category=SSFO.FL_FieldType where SD.cat_id=" + FieldsValues.CatId + " and SD.std_id =" + FieldsValues.StudentID + " ORDER BY SSFO.Sort_Order").Tables[0];

            dtDefaultfieldsvalue = DataConnection.ConnectionPath.Getdata("select FIELDS.Sort_Order,SD.value,SD.field_id,SD.field_category from (select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + FieldsValues.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_FormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + FieldsValues.SchoolID + " WHERE SSDF.Cat_Id=" + FieldsValues.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E')) FIELDS inner join student_details SD on SD.field_id=FIELDS.FieldId and SD.std_id=" + FieldsValues.StudentID + " and SD.cat_id=" + FieldsValues.CatId + "  ORDER BY FIELDS.Sort_Order").Tables[0];

            dtCoustmfieldsvalue = DataConnection.ConnectionPath.Getdata("select FIELDS.Sort_Order, SD.value,SD.field_id,SD.field_category from (select SSFO.Sort_Order,SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSCF.CustomField as IsMandatory from Student_Setup_Custom_Field SSCF left outer JOIN Student_Setup_FormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + FieldsValues.CatId + " and SSCF.sch_id=" + FieldsValues.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E')) FIELDS inner join student_details SD on SD.field_id=FIELDS.FieldId and SD.std_id=" + FieldsValues.StudentID + " and SD.cat_id=" + FieldsValues.CatId + " ORDER BY FIELDS.Sort_Order").Tables[0];

            dtDefaultfieldsvalue.Merge(dtCoustmfieldsvalue);
            DataView myDataView = dtDefaultfieldsvalue.DefaultView;
            myDataView.Sort = "Sort_Order";

            return myDataView.ToTable();

        }

        public void updatestudentdetails(StudentEditInfoBL UpdateFieldsValues)
        {

            String strStudentUpdate = "update student_details set value='" + UpdateFieldsValues.Value + "' where cat_id=" + UpdateFieldsValues.CatId + " and std_id=" + UpdateFieldsValues.StudentID + " and field_id=" + UpdateFieldsValues.FieldId + " and field_category='" + UpdateFieldsValues.FieldCategory + "'";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentUpdate);



        }

        public System.Data.DataTable getschoolspecificcategory(StudentEditInfoBL StudentEdit)
        {
            DataTable dt = null;
            object catagorylist = "";
            catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentEdit.SchoolID + "").Tables[0].Rows[0][0];

            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Category where Cat_Id in (" + catagorylist.ToString() + ")").Tables[0];
            return dt;

        }

        public System.Data.DataTable populateaddressfields(StudentEditInfoBL AddressFields)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtadditional = null;
                DataTable dtdefault = null;


                dtdefault = DataConnection.ConnectionPath.Getdata("select add_value as value,add_id as FieldId,'D' FieldCatagory,'0' Contact_set,cast(def_field_id as varchar(max)) from Student_Adddress_Details where std_id=" + AddressFields.StudentID + " and cat_id=" + AddressFields.CatId + " and sch_id=" + AddressFields.SchoolID + "").Tables[0];

                dtadditional = DataConnection.ConnectionPath.Getdata("select Value,Cont_Id as FieldId,'C' FieldCatagory,Contact_Set,'' def_field_id from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and Cat_Id=" + AddressFields.CatId + " and sch_Id=" + AddressFields.SchoolID + "").Tables[0];

                dtdefault.Merge(dtadditional);

                return dtdefault;
            }
            if (server == "MySQL")
            {
                DataTable dtadditional = null;
                DataTable dtdefault = null;


                dtdefault = DataConnection.ConnectionPath.Getdata("select add_value as value,add_id as FieldId,'D' FieldCatagory,'0' Contact_set,cast(def_field_id as char(6)) from Student_Adddress_Details where std_id=" + AddressFields.StudentID + " and cat_id=" + AddressFields.CatId + " and sch_id=" + AddressFields.SchoolID + "").Tables[0];

                dtadditional = DataConnection.ConnectionPath.Getdata("select Value,Cont_Id as FieldId,'C' FieldCatagory,Contact_Set,'' def_field_id from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and Cat_Id=" + AddressFields.CatId + " and sch_Id=" + AddressFields.SchoolID + "").Tables[0];

                dtdefault.Merge(dtadditional);

                return dtdefault;
            }
            else
            {

                DataTable dtdefault = null;

                return dtdefault;

            }

        }


        public System.Data.DataTable populateadditionaladdressfields(StudentEditInfoBL AddressFields)
        {

            DataTable dtadditional = null;

            dtadditional = DataConnection.ConnectionPath.Getdata("select Value,Cont_Id as FieldId,'C' FieldCatagory,Contact_Set,'' def_field_id from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and Cat_Id=" + AddressFields.CatId + " and sch_Id=" + AddressFields.SchoolID + " and Contact_Set=" + AddressFields.Set + "").Tables[0];

            return dtadditional;

        }

        public System.Data.DataTable getadditionaladdresscontactset(StudentEditInfoBL AddressFields)
        {
            if (server == "MSSQLServer")
            {

                DataTable dtadditionalset = null;

                dtadditionalset = DataConnection.ConnectionPath.Getdata("select SAC.Contact_Set,PR.person_stu_rel_type_nm + ' ' + 'Contact' as ContactName  from(select MIN(Std_Add_Cont_Id) as ID from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and sch_Id=" + AddressFields.SchoolID + "  group by Contact_Set) ViewSAC inner join Student_Additional_Contact SAC on ViewSAC.ID =SAC.Std_Add_Cont_Id inner join person_relationship PR on PR.person_rel_id =SAC.Value").Tables[0];

                return dtadditionalset;
            }
            if (server == "MySQL")
            {
                DataTable dtadditionalset = null;

                dtadditionalset = DataConnection.ConnectionPath.Getdata("select SAC.Contact_Set,concat( PR.person_stu_rel_type_nm , ' ' , 'Contact') as ContactName  from(select MIN(Std_Add_Cont_Id) as ID from Student_Additional_Contact where Std_Id=" + AddressFields.StudentID + " and sch_Id=" + AddressFields.SchoolID + "  group by Contact_Set) ViewSAC inner join Student_Additional_Contact SAC on ViewSAC.ID =SAC.Std_Add_Cont_Id inner join person_relationship PR on PR.person_rel_id =SAC.Value").Tables[0];

                return dtadditionalset;
            }
            else
            {
                DataTable dtadditionalset = null;

                return dtadditionalset;

            }

        }

        public void updateaddressdetails(StudentEditInfoBL UpdateFieldsValues)
        {

            String strStudentUpdate = "update Student_Adddress_Details set add_value='" + UpdateFieldsValues.Value + "' where std_id=" + UpdateFieldsValues.StudentID + " and cat_id=" + UpdateFieldsValues.CatId + " and sch_id =" + UpdateFieldsValues.SchoolID + " and add_id=" + UpdateFieldsValues.AddId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentUpdate);

        }

        public void updateadditionalcontactdetails(StudentEditInfoBL UpdateFieldsValues)
        {

            String strStudentUpdate = "update Student_Additional_Contact set Value='" + UpdateFieldsValues.Value + "' where Std_Id =" + UpdateFieldsValues.StudentID + " and Contact_Set=" + UpdateFieldsValues.Set + " and sch_Id=" + UpdateFieldsValues.SchoolID + " and Cont_set_Id=" + UpdateFieldsValues.ContSetId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentUpdate);

        }

        public int countsetfields(StudentEditInfoBL fieldcount)
        {
            object count;
            count = DataConnection.ConnectionPath.Getdata("select COUNT(*) from Student_Additional_Contact where Std_Id=" + fieldcount.StudentID + " and sch_Id=" + fieldcount.SchoolID + " group by Contact_Set").Tables[0].Rows[0][0];
            return Convert.ToInt32(count);
        }


        //*********************************photo save(saikat-14/10/2009)***********************************

        public void updatestudentphoto(StudentEditInfoBL student)
        {


            String strStudentAdd = "update student_picture set name = N'" + student.PhotoUrl + "' where std_id=" + student.StudentID + " and Sch_id=" + student.SchoolID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strStudentAdd);

        }


        public int getphotocount(StudentEditInfoBL photocount)
        {
            object count;
            count = DataConnection.ConnectionPath.Getdata("SELECT COUNT(*) FROM student_picture WHERE std_id=" + photocount.StudentID + " and Sch_id=" + photocount.SchoolID + "").Tables[0].Rows[0][0];
            return Convert.ToInt32(count);
        }


        public int checkfieldvalues(StudentEditInfoBL fieldcount)
        {
            object count;
            count = DataConnection.ConnectionPath.Getdata("select COUNT(*) from student_details where field_id=" + fieldcount.FieldId + " and cat_id=" + fieldcount.CatId + " and std_id=" + fieldcount.StudentID + "").Tables[0].Rows[0][0];
            return Convert.ToInt32(count);
        }

        //*********************************photo save(saikat-14/10/2009)***********************************

        public System.Data.DataTable populatemedicalinfovalue(StudentEditInfoBL MedicalInfo)
        {

            DataTable dt = null;

            dt = DataConnection.ConnectionPath.Getdata("select Value,Med_Info_Id as FieldId from Student_Medical_Information_Details where Sch_Id=" + MedicalInfo.SchoolID + " and Std_Id =" + MedicalInfo.StudentID + "").Tables[0];

            return dt;

        }

        public void deleteallmedicalinformation(StudentEditInfoBL MedicalInfo)
        {
            if (server == "MSSQLServer")
            {
                String deleteallmedicalinfo = "deleteAllMedicalInformation " + MedicalInfo.StudentID + "," + MedicalInfo.SchoolID + "";

                DataConnection.ConnectionPath.ExecuteCommand(deleteallmedicalinfo);
            }

        }
        public void updatemedicalinfo(StudentEditInfoBL MedicalInfo)
        {


            String strmedicalinfo = "update Student_Medical_Information_Details set Value='" + MedicalInfo.Value.Replace("'", "''") + "' where Sch_Id=" + MedicalInfo.SchoolID + " and Std_Id=" + MedicalInfo.StudentID + " and Med_Info_Id=" + MedicalInfo.MedInfoId + "";

            DataConnection.ConnectionPath.ExecuteCommand(strmedicalinfo);

        }

        public System.Data.DataTable PopulateSchoolFieldValue(StudentEditInfoBL FieldsValues)
        {

            DataTable dtDefaultfieldsvalue = null;
            DataTable dtCoustmfieldsvalue = null;

            //  dtfieldsvalue = DataConnection.ConnectionPath.Getdata("select value,field_id,field_category from student_details where cat_id=" + FieldsValues.CatId + " and std_id =" + FieldsValues.StudentID + "").Tables[0];
            //dtfieldsvalue = DataConnection.ConnectionPath.Getdata("select SD.value,SD.field_id,SD.field_category from student_details SD left outer join Student_Setup_FormLayout_Order SSFO on SD.field_id=SSFO.Field_Id AND SD.field_category=SSFO.FL_FieldType where SD.cat_id=" + FieldsValues.CatId + " and SD.std_id =" + FieldsValues.StudentID + " ORDER BY SSFO.Sort_Order").Tables[0];

            dtDefaultfieldsvalue = DataConnection.ConnectionPath.Getdata("select FIELDS.Sort_Order,SD.value,SD.field_id,SD.field_category from (select SSDF.Def_Field_Id as FieldId,SSDF.field_name as FieldName,SSDF.Field_Type as FieldType,SSDFS.Status as Status,SSDF.DefaultField as Default_Custom,SSDF.Status as IsMandatory,SSFO.* from Student_Setup_Default_Field SSDF left outer join(select * from Student_Setup_Default_Field_Status where School_Id =" + FieldsValues.SchoolID + " and Status='A')as SSDFS on SSDF.Def_Field_Id=SSDFS.Default_Field_Id left outer JOIN Student_Setup_FormLayout_Order SSFO on SSDF.Def_Field_Id=SSFO.Field_Id AND SSDF.DefaultField=SSFO.FL_FieldType AND SSFO.Sch_Id=" + FieldsValues.SchoolID + " WHERE SSDF.Cat_Id=" + FieldsValues.CatId + " and (SSDFS.Status!='I' or SSDFS.Status IS NULL) and (SSDFS.Field_View='V_E' or SSDFS.Field_View='E')) FIELDS inner join student_details SD on SD.field_id=FIELDS.FieldId and SD.std_id=" + FieldsValues.StudentID + " and SD.cat_id=" + FieldsValues.CatId + "  ORDER BY FIELDS.Sort_Order").Tables[0];

            dtCoustmfieldsvalue = DataConnection.ConnectionPath.Getdata("select FIELDS.Sort_Order, SD.value,SD.field_id,SD.field_category from (select SSFO.Sort_Order,SSCF.extrafield_id as FieldId, SSCF.field_name as FieldName,SSCF.field_type_id as FieldType,SSCF.Status as Status,SSCF.CustomField as Default_Custom,SSCF.CustomField as IsMandatory from Student_Setup_Custom_Field SSCF left outer JOIN Student_Setup_FormLayout_Order SSFO on SSCF.extrafield_id=SSFO.Field_Id and SSCF.CustomField=SSFO.FL_FieldType where Category_Id=" + FieldsValues.CatId + " and SSCF.sch_id=" + FieldsValues.SchoolID + " and SSCF.Status!='I' and (SSCF.Field_View='V_E' or SSCF.Field_View='E')) FIELDS inner join student_details SD on SD.field_id=FIELDS.FieldId and SD.std_id=" + FieldsValues.StudentID + " and SD.cat_id=" + FieldsValues.CatId + " ORDER BY FIELDS.Sort_Order").Tables[0];

            dtDefaultfieldsvalue.Merge(dtCoustmfieldsvalue);
            DataView myDataView = dtDefaultfieldsvalue.DefaultView;
            myDataView.Sort = "Sort_Order";

            return myDataView.ToTable();

        }

        //**************19/4/2010****************
        public void deleteProgramsSpecialEducationDetails(StudentEditInfoBL MedicalInfo)
        {
            if (server == "MSSQLServer")
            {
                String deleteallmedicalinfo = "delete from Student_SetUp_Programs_Special_Education_Details where std_id=" + MedicalInfo.StudentID + " and sch_id=" + MedicalInfo.SchoolID + "";

                DataConnection.ConnectionPath.ExecuteCommand(deleteallmedicalinfo);
            }

        }

        public void deleteProgramsGiftedDetails(StudentEditInfoBL MedicalInfo)
        {
            if (server == "MSSQLServer")
            {
                String deleteallmedicalinfo = "delete from Student_Setup_Programs_Gifted_Details where std_id=" + MedicalInfo.StudentID + " and sch_id=" + MedicalInfo.SchoolID + "";

                DataConnection.ConnectionPath.ExecuteCommand(deleteallmedicalinfo);
            }

        }

    }

    
}
